Re: Improving the speed of an UPDATE (evolved from Re: [GENERAL] Stuck in a vacuum.)

>Stuart Rison wrote:
>> I am presuming that you can't just willy-nilly increase the size of -B and
>> that the extent you can increase it by is dependant on available memory.
>                                                         ^^^^^^^^^^^^^^^^
>It depends on available _shared_ memory.
>-B 256 or -B 512 is nice.

well I'm currently running my postmaster with -B 512.  Would it help if I
went to -B 1024?  Or can I even do so (is there a way of checking the
maximum -B the system could support)?

>>
>> >Also, I don't know has Indigo TEST AND SET or not.
>
>Look in .../pgsql/src/include/os.h

checked.  if have #define HAS_TEST_AND-SET

>> >400,000 rows is not so much for PG.
>>
>> Yes, I thought 400,000 rows should not be too much of a problem.  I don't
>
>BTW, did you setup indices? Did you use EXPLAIN for your queries?

Table    = blast_hits
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| hit_id                           | int4 default nextval ( 'hit_id_s |     4 |
| query_id                         | text                             |   var |
| pdb_id                           | char()                           |     4 |
| chain                            | char()                           |     1 |
| fragment                         | char()                           |     1 |
| high_score                       | int2                             |     2 |
| prob_score                       | float8                           |     8 |
| number_hsps                      | int2                             |     2 |
| species                          | char()                           |     3 |
+----------------------------------+----------------------------------+-------+
Indices:  blast_hits_prob_score_idx
          hit_id_idx
          query_id_idx

all indices are b-trees.

and it contains 400,000 rows.

my query which took very long (over 15minutes and the I killed it) was:

UPDATE blast_hits SET hit_id=hit_id+400000 WHERE hit_id<=208000;

functions=> explain update blast_hits set hit_id=hit_id+400000 where
hit_id<=208000;
NOTICE:  QUERY PLAN:

Index Scan using hit_id_idx on blast_hits  (cost=8302.12 size=125503 width=82)

EXPLAIN
functions=> explain update blast_hits set hit_id=hit_id+400000;
NOTICE:  QUERY PLAN:

Seq Scan on blast_hits  (cost=17100.73 size=376507 width=82)

EXPLAIN

I guess the question here is, is an UPDATE akin to a INSERT or not?
Because if it is, I am under the impression that index radically slow down
inserts and so perhaps I should drop all indices before the UPDATE and the
rebuild them after...
but if it isn't, then the index is in fact being used by the UPDATE with
the WHERE clause.

>> I was also under the impression that it had a greatly improved query
>> optimizer.  Would it make an difference with two-way and three-way joins?
>
>Yes.

It may well be time for the big move!

regards,

Stuart.

+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+